package com.awspaas.user.apps.cpq.controller;

import java.io.IOException;
import java.util.Arrays;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.CellValue;
import org.apache.poi.ss.usermodel.FormulaEvaluator;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.jsoup.Jsoup;
import org.jsoup.nodes.Document;
import org.openxmlformats.schemas.spreadsheetml.x2006.main.STConditionalFormattingOperator;

import com.actionsoft.bpms.bo.engine.BO;
import com.actionsoft.bpms.commons.database.RowMap;
import com.actionsoft.bpms.commons.mvc.view.ResponseObject;
import com.actionsoft.bpms.server.UserContext;
import com.actionsoft.bpms.util.DBSql;
import com.actionsoft.sdk.local.SDK;
import com.alibaba.fastjson.JSONArray;
import com.alibaba.fastjson.JSONObject;
import com.ibm.db2.jcc.am.l;

public class CPQ_GetTemplateAllModuleNumberWeb {

	public String getTemplateAllModuleNumber(UserContext userContext, String bindId, String cs, String datas) {
		//XSSFWorkbook workbookwl = new XSSFWorkbook();
		//XSSFWorkbook workbook = new XSSFWorkbook();
		

			// 将数组转换为列表
			 JSONArray list=JSONObject.parseArray(cs);		
			JSONArray mList=JSONObject.parseArray(datas);	
			// 获取模块所需参数信息
			Map<String, String> pMap = new HashMap<String, String>();
			Map<String, String> nameMap = new HashMap<String, String>();
			Map<String, String> noMap = new HashMap<>();
			for (int i = 0; i < list.size(); i++) {
				JSONObject json = (JSONObject) list.get(i);
				String bh = (String) json.get("PARMENTIER_NO");
				String value = (String) json.get("PARMENTIER_VALUE");
				String name = (String) json.get("PARMENTIER_NAME");
				String dw = (String) json.get("DW");
				pMap.put(bh, value);
				nameMap.put(bh, name);
				int num = i + 1;
				noMap.put(bh, "B$" + num);
			}
			System.out.println("pMap:"+pMap.toString());
			// 获取模块编码信息
			Map<String, JSONObject> idMap = new HashMap<>();
			String bms = "";
			for (Object string : mList) {
				JSONObject json = (JSONObject) string;
				String bm = json.getString("MKBH");
				if (bms.isEmpty()) {
					bms = "'" + bm + "'";
				} else {
					bms += ",'" + bm + "'";
				}
				String id = json.getString("ID");
				idMap.put(id, json);
			}
			String sql = "select  * from  BO_EU_CPQ_MODULE_MSG where   STATUS='启用' and MKBM in (" + bms + ")";
			List<RowMap> maps = DBSql.getMaps(sql);
			Map<String, RowMap> moduleMap = new HashMap<>();
			if (maps.size() > 0) {
				for (RowMap rowMap : maps) {
					String mkbm = rowMap.getString("MKBM");
					moduleMap.put(mkbm, rowMap);
				}
			}
			// 对模块数量和模块单价进行计算
			List<BO> bos = SDK.getBOAPI().query("BO_EU_CPQ_TEMPLATE_PRICE_MODULE").bindId(bindId).list();
			//存放已经保存的数据
			Map<String, BO> boMap = new HashMap<>();
			for (BO bo : bos) {
				boMap.put(bo.getString("ID"), bo);
			}
			for (String  id : idMap.keySet()) {
				Workbook workbookwl = new XSSFWorkbook();
				Workbook workbook = new XSSFWorkbook();
				try {													
				// 获取对应编码的数据信息；
				if (idMap.containsKey(id)) {
					JSONObject json = idMap.get(id);
					String bm = json.getString("MKBH");
					// 获取模块维护台账的模块数据
					RowMap rowMap = moduleMap.get(bm);
					String type = rowMap.getString("SITRUE");// 是否动态计算单价模块
					double MKDJ = Double.parseDouble(rowMap.getString("MKDJ"));// 模块单价
					double TJ = Double.parseDouble(rowMap.getString("TJ"));// 体积
					double ZL = Double.parseDouble(rowMap.getString("ZL"));// 重量
					double CLF = Double.parseDouble(rowMap.getString("CLF"));// 材料费
					double RGF = Double.parseDouble(rowMap.getString("RGF"));// 人工费
					double WXWG = Double.parseDouble(rowMap.getString("WXWG"));// 外协费
					double CGF = Double.parseDouble(rowMap.getString("CGF"));// 采购费
					// 如果是则进行动态计算
					if (type.equals("true")) {

						// 计算当前模块中物料明细的数量和物料的成本价格，根据物料明细的表中的计算规则进行计算。
						/**
						 * 获取模块中的物料明细，遍历物料明细判断当前物料是否是需要计算的，是则通过计算规则进行计算，如果不是则不需要计算。
						 */
						List<BO> materials = SDK.getBOAPI().query("BO_EU_CPQ_MODULE_MSG_GRID")
								.bindId(rowMap.getString("BINDID")).list();
						/**
						 * 计算物料数量和成本。
						 */

						/************************** 通过excel公式进行计算 ***********************************/
						Map<String, BO> materMap = new HashMap<>();
						//XSSFWorkbook workbookwl = new XSSFWorkbook();
						//XSSFWorkbook workbook = new XSSFWorkbook();
						// 创建公式评估器
						// 设置单元格值
						Sheet sheet = workbookwl.createSheet("Sheet1");
						for (int i = 0; i < list.size(); i++) {
							int num = i + 1;
							Cell cellB1 = sheet.createRow(i).createCell(1);
							JSONObject jsonObject = (JSONObject) list.get(i);
							String bh = (String) jsonObject.get("PARMENTIER_NO");
							cellB1.setCellValue(pMap.get(bh));
						}
						// 将物料中的计算规则的参数替换为指定的excel参数
						if (materials.size() > 0) {
							for (int i = 0; i < materials.size(); i++) {
								BO bo = materials.get(i);
								String equation = bo.getString("WLJSGS");
								  Document document = Jsoup.parse(equation);
								  String newQuation=document.text();
								if (!newQuation.equals("0")&&!newQuation.isEmpty()) {
									System.out.println("参数计算公式："+newQuation);
									// 获取物料数量计算公式中参数代号，如果有则将代号存放在list中
									for (String key : pMap.keySet()) {
										int num = newQuation.indexOf(key);
										if (num > -1) {
											newQuation = newQuation.replace(key, noMap.get(key));
										}
									}
									System.out.println("转换后的参数计算公式："+newQuation);
									
									// 计算出物料数量和当前模块的物料成本价格
									FormulaEvaluator formulaEvaluator = workbookwl.getCreationHelper().createFormulaEvaluator();
									Cell cellB1 = sheet.createRow(list.size()+i).createCell(2);
									cellB1.setCellFormula(newQuation);
									formulaEvaluator.evaluateFormulaCell(cellB1);
									// 获取公式计算结果
									CellValue cellValue = formulaEvaluator.evaluate(cellB1);
									System.out.println("The result of the formula is: " + cellValue.getNumberValue());
									String price = bo.getString("DJ").isEmpty()?"0":bo.getString("DJ");
									String zl = bo.getString("ZL").isEmpty()?"0":bo.getString("ZL");
									String tj = bo.getString("TJ").isEmpty()?"0":bo.getString("TJ");
									String clf = bo.getString("CLF").isEmpty()?"0":bo.getString("CLF");
									String rgf = bo.getString("RGF").isEmpty()?"0":bo.getString("RGF");
									String wx = bo.getString("WXWG").isEmpty()?"0":bo.getString("WXWG");
									String cgf = bo.getString("CGF").isEmpty()?"0":bo.getString("CGF");
									double total = Double.parseDouble(price) * cellValue.getNumberValue();
									double zltotal = Double.parseDouble(zl) * cellValue.getNumberValue();
									double tjtotal = Double.parseDouble(tj) * cellValue.getNumberValue();
									double clftotal = Double.parseDouble(clf) * cellValue.getNumberValue();
									double rgftotal = Double.parseDouble(rgf) * cellValue.getNumberValue();
									double wxtotal = Double.parseDouble(wx) * cellValue.getNumberValue();
									double cgftotal = Double.parseDouble(cgf) * cellValue.getNumberValue();
									// 将数量和成本价格放入物料map集合中
									bo.set("SL", cellValue.getNumberValue());
									bo.set("ZJ", total);
									bo.set("ZZL", zltotal);
									bo.set("ZTJ", tjtotal);
									bo.set("ZCGF", clftotal);
									bo.set("ZGSF", rgftotal);
									bo.set("ZWX", wxtotal);
									bo.set("ZCLF", cgftotal);
								}
								materMap.put(bo.getString("WLBM"), bo);
							}
						}
						// 计算出模块的单价，每个物料总价相加
						double mkPrice = 0;
						double zzlPrice = 0;
						double ztjPrice = 0;
						double zcgfPrice = 0;
						double zgsfPrice = 0;
						double zwxPrice = 0;
						double zclfPrice = 0;
						for (String key : materMap.keySet()) {
							BO bo = materMap.get(key);
							String zjString = bo.getString("ZJ");
							String zzlString = bo.getString("ZZL");
							String ztjString = bo.getString("ZTJ");
							String zcgfString = bo.getString("ZCGF");
							String zgsfString = bo.getString("ZGSF");
							String zwxString = bo.getString("ZWX");
							String zclfString = bo.getString("ZCLF");
							mkPrice = mkPrice + Double.parseDouble(zjString);
							zzlPrice = zzlPrice + Double.parseDouble(zzlString);
							ztjPrice = ztjPrice + Double.parseDouble(ztjString);
							zcgfPrice = zcgfPrice + Double.parseDouble(zcgfString);
							zgsfPrice = zgsfPrice + Double.parseDouble(zgsfString);
							zwxPrice = zwxPrice + Double.parseDouble(zwxString);
							zclfPrice = zclfPrice + Double.parseDouble(zclfString);
						}
						if (MKDJ == 0) {
							MKDJ = mkPrice;
							TJ = ztjPrice;
							ZL = zzlPrice;
							CLF = zclfPrice;
							RGF = zgsfPrice;
							WXWG = zwxPrice;
							CGF = zcgfPrice;
						}
						/************************** 通过excel公式进行计算 ***********************************/
					} 
					String rule = rowMap.getString("SLJSGZ"); // 计算规则
					/**
					 * 计算模块数量。
					 */
					if (!rule.equals("0") && !rule.isEmpty()) {
						Document document = Jsoup.parse(rule);
						  String newRule=document.text();
						Map<String, BO> materMap = new HashMap<>();
						System.out.println("模块计算规则新："+newRule);
						// 创建公式评估器
						//XSSFWorkbook workbookwl = new XSSFWorkbook();
						//XSSFWorkbook workbook = new XSSFWorkbook();
						FormulaEvaluator formulaEvaluator = workbook.getCreationHelper().createFormulaEvaluator();
						// 设置单元格值
						if(!type.equals("true")) {
							Sheet sheet = workbook.createSheet("Sheet1");
							for (int i = 0; i < list.size(); i++) {
								int num = i + 1;
								Cell cellB1 = sheet.createRow(i).createCell(1);
								JSONObject jsonObject = (JSONObject) list.get(i);
								String bh = (String) jsonObject.get("PARMENTIER_NO");
								cellB1.setCellValue(pMap.get(bh));
								System.out.println("cellB1:"+cellB1.getStringCellValue());
							}
							
							
							
						}
						// 将物料中的计算规则的参数替换为指定的excel参数
						// 获取物料数量计算公式中参数代号，如果有则将代号存放在list中
						for (String key : pMap.keySet()) {
							int num = newRule.indexOf(key);
							if (num > -1) {
								newRule = newRule.replace(key, noMap.get(key));
							}
						}
						Sheet sheet =workbook.getSheet("Sheet1");
						if(null==sheet) {
							 sheet = workbook.createSheet("Sheet1");
								for (int i = 0; i < list.size(); i++) {
									int num = i + 1;
									Cell cellB1 = sheet.createRow(i).createCell(1);
									JSONObject jsonObject = (JSONObject) list.get(i);
									String bh = (String) jsonObject.get("PARMENTIER_NO");
									cellB1.setCellValue(pMap.get(bh));
									System.out.println("cellB1:"+cellB1.getStringCellValue());
								}
						}
						// 计算出物料数量和当前模块的物料成本价格
						Cell cellB1 = sheet.createRow(list.size()+1).createCell(3);
						cellB1.setCellFormula(newRule);
						System.out.println("cellB1value:"+cellB1.getCellFormula());
						formulaEvaluator.evaluateFormulaCell(cellB1);
						// 获取公式计算结果
						CellValue cellValue = formulaEvaluator.evaluate(cellB1);
						System.out.println("The result of the formula is: " + cellValue.getNumberValue());
						double number = cellValue.getNumberValue();
						// 计算出模块数量后，需要计算出模块的管理费GLFY、制造费用ZZFY、体积TJ、重量ZL
						// 总成本价
						double MKDJ_price = MKDJ;
						double MKTotalPrice = number * MKDJ_price;
						double zlTotalPrice = number * ZL;
						double tjTotalPrice = number * TJ;
						double clfTotalPrice = number * CLF;
						double rgfTotalPrice = number * RGF;
						double wxTotalPrice = number * WXWG;
						double cgfTotalPrice = number * CGF;
						// 将计算好的数据放入选择的子表数据中，并更新当前数据。
						if(boMap.containsKey(id)) {
						BO mbo=	boMap.get(id);
						mbo.set("DJ", MKDJ);// 模块单价
						mbo.set("SL", number);// 模块数量
						mbo.set("DW", rowMap.getString("DW"));// 模块单位
						mbo.set("GG", rowMap.getString("GG"));// 规格
						mbo.set("ZJ", MKTotalPrice);// 模块总价
						mbo.set("ZZL", zlTotalPrice);// 总重量
						mbo.set("ZTJ", tjTotalPrice);// 模块总体积
						mbo.set("ZCLF", clfTotalPrice);// 模块总材料费
						mbo.set("ZGSF", rgfTotalPrice);// 模块总人工费
						mbo.set("ZWXWG", wxTotalPrice);// 模块总外协
						mbo.set("ZCGF", cgfTotalPrice);// 模块总采购费
						mbo.set("ZL", ZL);// 重量
						mbo.set("TJ", TJ);// 模块体积
						mbo.set("CLF", CLF);// 模块材料费
						mbo.set("RGF", RGF);// 模块人工费
						mbo.set("WXWG", WXWG);// 模块外协
						mbo.set("CGF", CGF);// 模块采购费
						mbo.set("XDXH", rowMap.getString("MKMC"));// 选定型号
						mbo.set("MKBH", rowMap.getString("MKBM"));// 模块编码
						mbo.set("BZ", rowMap.getString("BZ"));// 适用备注说明
						mbo.set("BBH", rowMap.getString("BBH"));// 版本号
						mbo.set("VERSION", rowMap.getString("VERSION"));// 版本
						mbo.set("XTMC", json.getString("XTMC"));// 系统名称
						mbo.set("MKMC", json.getString("MKMC"));// 模块名称
						mbo.set("PX",json.getString("PX"));// 系统序号
						// mbo.set("CSSM", msg);//参数说明
						SDK.getBOAPI().update("BO_EU_CPQ_TEMPLATE_PRICE_MODULE", mbo);
						}else {
							BO mbo=	new BO();
							mbo.set("DJ", MKDJ);// 模块单价
							mbo.set("SL", number);// 模块数量
							mbo.set("DW", rowMap.getString("DW"));// 模块单位
							mbo.set("GG", rowMap.getString("GG"));// 规格
							mbo.set("ZJ", MKTotalPrice);// 模块总价
							mbo.set("ZZL", zlTotalPrice);// 总重量
							mbo.set("ZTJ", tjTotalPrice);// 模块总体积
							mbo.set("ZCLF", clfTotalPrice);// 模块总材料费
							mbo.set("ZGSF", rgfTotalPrice);// 模块总人工费
							mbo.set("ZWXWG", wxTotalPrice);// 模块总外协
							mbo.set("ZCGF", cgfTotalPrice);// 模块总采购费
							mbo.set("ZL", ZL);// 重量
							mbo.set("TJ", TJ);// 模块体积
							mbo.set("CLF", CLF);// 模块材料费
							mbo.set("RGF", RGF);// 模块人工费
							mbo.set("WXWG", WXWG);// 模块外协
							mbo.set("CGF", CGF);// 模块采购费
							mbo.set("XDXH", rowMap.getString("MKMC"));// 选定型号
							mbo.set("MKBH", rowMap.getString("MKBM"));// 模块编码
							mbo.set("BZ", rowMap.getString("BZ"));// 适用备注说明
							mbo.set("BBH", rowMap.getString("BBH"));// 版本号
							mbo.set("VERSION", rowMap.getString("VERSION"));// 版本
							mbo.set("XTMC", json.getString("XTMC"));// 系统名称
							mbo.set("MKMC", json.getString("MKMC"));// 模块名称
							mbo.set("PX",json.getString("PX"));// 系统序号
							// mbo.set("CSSM", msg);//参数说明
							SDK.getBOAPI().create("BO_EU_CPQ_TEMPLATE_PRICE_MODULE", mbo, bindId, userContext.getUID());
						}
						

					} else {
						if(boMap.containsKey(id)) {
							BO mbo=	boMap.get(id);
							mbo.set("DJ", MKDJ);// 模块单价
							mbo.set("SL", json.getString("SL"));// 模块数量
							mbo.set("DW", json.getString("DW"));// 模块单位
							mbo.set("GG", json.getString("GG"));// 规格
							mbo.set("ZJ", json.getString("ZJ"));// 模块总价
							mbo.set("ZZL", json.getString("ZZL"));// 总重量
							mbo.set("ZTJ", json.getString("ZTJ"));// 模块总体积
							mbo.set("ZCLF", json.getString("ZCLF"));// 模块总材料费
							mbo.set("ZGSF", json.getString("ZRGF"));// 模块总人工费
							mbo.set("ZWXWG", json.getString("ZWXWG"));// 模块总外协
							mbo.set("ZCGF", json.getString("ZCGF"));// 模块总采购费
							mbo.set("ZL", ZL);// 重量
							mbo.set("TJ", TJ);// 模块体积
							mbo.set("CLF", CLF);// 模块材料费
							mbo.set("RGF", RGF);// 模块人工费
							mbo.set("WXWG", WXWG);// 模块外协
							mbo.set("CGF", CGF);// 模块采购费
							mbo.set("XDXH", json.getString("XDXH"));// 选定型号
							mbo.set("MKBH", json.getString("MKBH"));// 模块编码
							mbo.set("BZ", json.getString("BZ"));// 适用备注说明
							mbo.set("BBH", json.getString("BBH"));// 版本号
							mbo.set("VERSION", json.getString("VERSION"));// 版本
							mbo.set("XTMC", json.getString("XTMC"));// 系统名称
							mbo.set("MKMC", json.getString("MKMC"));// 模块名称
							mbo.set("PX",json.getString("PX"));// 系统序号
							// mbo.set("CSSM", msg);//参数说明
							SDK.getBOAPI().update("BO_EU_CPQ_TEMPLATE_PRICE_MODULE", mbo);
						}else {
							BO mbo=	new BO();
							mbo.set("DJ", MKDJ);// 模块单价
							mbo.set("SL", json.getString("SL"));// 模块数量
							mbo.set("DW", json.getString("DW"));// 模块单位
							mbo.set("GG", json.getString("GG"));// 规格
							mbo.set("ZJ", json.getString("ZJ"));// 模块总价
							mbo.set("ZZL", json.getString("ZZL"));// 总重量
							mbo.set("ZTJ", json.getString("ZTJ"));// 模块总体积
							mbo.set("ZCLF", json.getString("ZCLF"));// 模块总材料费
							mbo.set("ZGSF", json.getString("ZRGF"));// 模块总人工费
							mbo.set("ZWXWG", json.getString("ZWXWG"));// 模块总外协
							mbo.set("ZCGF", json.getString("ZCGF"));// 模块总采购费
							mbo.set("ZL", ZL);// 重量
							mbo.set("TJ", TJ);// 模块体积
							mbo.set("CLF", CLF);// 模块材料费
							mbo.set("RGF", RGF);// 模块人工费
							mbo.set("WXWG", WXWG);// 模块外协
							mbo.set("CGF", CGF);// 模块采购费
							mbo.set("XDXH", json.getString("XDXH"));// 选定型号
							mbo.set("MKBH", json.getString("MKBH"));// 模块编码
							mbo.set("BZ", json.getString("BZ"));// 适用备注说明
							mbo.set("BBH", json.getString("BBH"));// 版本号
							mbo.set("VERSION", json.getString("VERSION"));// 版本
							mbo.set("XTMC", json.getString("XTMC"));// 系统名称
							mbo.set("MKMC", json.getString("MKMC"));// 模块名称
							mbo.set("PX",json.getString("PX"));// 系统序号
							SDK.getBOAPI().create("BO_EU_CPQ_TEMPLATE_PRICE_MODULE", mbo, bindId, userContext.getUID());
						}
						
					}
				}	
				} catch (Exception e) {
					throw e;
				}finally {
					try {
						workbookwl.close();
					} catch (IOException e) {
						// TODO Auto-generated catch block
						e.printStackTrace();
					}
		  			 try {
						workbook.close();
					} catch (IOException e) {
						// TODO Auto-generated catch block
						e.printStackTrace();
					}
				} 
			}
			
			
			
			ResponseObject ro = ResponseObject.newOkResponse();
			return ro.toString();
		

	}

}
